package com.examsys.dao;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Onlines;
import com.examsys.po.Paper;
import com.examsys.po.Users;

/**
 * 考试者在线考试的时间记录数据访问层接口
 * @author edu-1
 *
 */
public class OnlinesDaoImpl extends AbstractBaseDao<Onlines, Integer> implements OnlinesDao {

	/**
	 * 添加考试者在线考试的时间记录
	 */
	@Override
	public void add(Onlines obj) throws Exception {
		//构造插入语句
		String sql="Insert into ONLINES (ID,USER_ID,PAPER_ID,LAST_TIME,IP) values (ONLINES_ID_SEQ.nextval,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getUsers().getId(),obj.getPaper().getId(),obj.getLast_time(),obj.getIp()});
	}

	/**
	 * 更新考试者在线考试的时间记录
	 */
	@Override
	public void update(Onlines obj) throws Exception {
		//构造更新语句
		String sql="UPDATE ONLINES SET USER_ID=?,PAPER_ID=?,LAST_TIME=?,IP=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getUsers().getId(),obj.getPaper().getId(),obj.getLast_time(),obj.getIp(),obj.getId()});
	}

	/**
	 * 删除考试者在线考试的时间记录
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM ONLINES WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 获取考试者在线考试的时间记录
	 */
	@Override
	public Onlines get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.LAST_TIME,a.IP,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM ONLINES a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		String ip = (String)m.get("IP");//登录进来的IP
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp last_time = (Timestamp)m.get("LAST_TIME");//最后一次登录时间
		Date last_time2 = new Date(last_time.getTime());//需要转换一下时间
		
		Integer user_id = (Integer)m.get("USER_ID");//编号
		String user_name = (String)m.get("USER_NAME");//会员名
		String real_name = (String)m.get("REAL_NAME");//真实姓名
		String user_no = (String)m.get("USER_NO");//学号
		
		Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
		String paper_name = (String)m.get("PAPER_NAME");//试卷名称
		
		Onlines onlines=new Onlines();//创建实体类对象
		onlines.setId(idd);
		onlines.setIp(ip);
		onlines.setLast_time(last_time2);
		
		Users users=new Users();//创建实体类对象
		users.setId(user_id);
		users.setUser_name(user_name);
		users.setUser_no(user_no);
		users.setReal_name(real_name);
		
		Paper paper=new Paper();//创建实体类对象
		paper.setId(paper_id);
		paper.setPaper_name(paper_name);
		
		onlines.setPaper(paper);//设置关联对象
		onlines.setUsers(users);//设置关联对象
		return onlines;
	}

	/**
	 * 获取所有考试者在线考试的时间记录
	 */
	@Override
	public List<Onlines> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.LAST_TIME,a.IP,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM ONLINES a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Onlines> list=new ArrayList<Onlines>();
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			String ip = (String)m.get("IP");//登录进来的IP
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp last_time = (Timestamp)m.get("LAST_TIME");//最后一次登录时间
			Date last_time2 = new Date(last_time.getTime());//需要转换一下时间
			
			Integer user_id = (Integer)m.get("USER_ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String user_no = (String)m.get("USER_NO");//学号
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			Onlines onlines=new Onlines();//创建实体类对象
			onlines.setId(idd);
			onlines.setIp(ip);
			onlines.setLast_time(last_time2);
			
			Users users=new Users();//创建实体类对象
			users.setId(user_id);
			users.setUser_name(user_name);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			onlines.setPaper(paper);//设置关联对象
			onlines.setUsers(users);//设置关联对象
			list.add(onlines);
		}
		return list;
	}

	/**
	 * 带条件获取考试者在线考试的时间记录
	 */
	@Override
	public List<Onlines> getList(Onlines obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.LAST_TIME,a.IP,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM ONLINES a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getId()!=null
					&&obj.getUsers().getId()!=0){
				sql+=" AND a.USER_ID="+obj.getUsers().getId();
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getUser_name()!=null
					&&!"".equals(obj.getUsers().getUser_name())){
				sql+=" AND c.USER_NAME='"+obj.getUsers().getUser_name()+"'";
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getUser_no()!=null
					&&!"".equals(obj.getUsers().getUser_no())){
				sql+=" AND c.USER_NO='"+obj.getUsers().getUser_no()+"'";
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getReal_name()!=null
					&&!"".equals(obj.getUsers().getReal_name())){
				sql+=" AND c.Real_NAME='"+obj.getUsers().getReal_name()+"'";
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getId()!=null
					&&obj.getPaper().getId()!=0){
				sql+=" AND b.PAPER_ID="+obj.getPaper().getId();
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getPaper_name()!=null
					&&!"".equals(obj.getPaper().getPaper_name())){
				sql+=" AND b.PAPER_NAME='"+obj.getPaper().getPaper_name()+"'";
			}
			
		}

		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Onlines> list=new ArrayList<Onlines>();
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			String ip = (String)m.get("IP");//登录进来的IP
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp last_time = (Timestamp)m.get("LAST_TIME");//最后一次登录时间
			Date last_time2 = new Date(last_time.getTime());//需要转换一下时间
			
			Integer user_id = (Integer)m.get("USER_ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String user_no = (String)m.get("USER_NO");//学号
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			Onlines onlines=new Onlines();//创建实体类对象
			onlines.setId(idd);
			onlines.setIp(ip);
			onlines.setLast_time(last_time2);
			
			Users users=new Users();//创建实体类对象
			users.setId(user_id);
			users.setUser_name(user_name);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			onlines.setPaper(paper);//设置关联对象
			onlines.setUsers(users);//设置关联对象
			list.add(onlines);
		}
		return list;
	}

}
